<?php

/*
 * This file will perform the ajax for editing a route.
 * This is being used by admRouteEdit.php.
 * 
 * Created by: Peter Agno Jr.
 * Date created: December 15, 2011
 * 
 * In parameters: routeId, routeName, documentSetupId, subRouteIds, newSubRouteIds, newSubRouteNames, subRouteSteps, recipientRoles, canBeCancelled, reqAttach, recipientRow, userActivity, category
 * Out parameters: flag and msg about the success of creating the route
 */

//********************************************************************************************************
/*
 * Define the functions in here
 */

    // Start - Check for the duplication of name
    function nameNotExist($routeName) {
        $query =
            "
                SELECT *
                FROM DocumentRoute
                WHERE routeName = '$routeName'
            ";
        
        $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        
        if ( mysql_num_rows($result) == 0 ) {
            return true;
        }
        else {
            return false;
        }
    } // End - Check for the duplication of name

    // Start - Check $newSubRouteIds if there is already a created one in SubRoute
    function checkNewSubRouteId($newSubRouteIds) {
        $cont = true;
        $duplicateSubRouteId = "";
        $newSubRouteIds = explode(",", $newSubRouteIds);
        
        for ( $x = 0 ; $x < count($newSubRouteIds) - 1 && $cont == true ; $x++ ) {
            $tempId = explode("_", $newSubRouteIds[$x]);
            
            $query = 
                "
                    SELECT *
                    FROM SubRoute         
                    WHERE subRouteId = '$tempId[0]'
                ";
            
            $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        
            if ( mysql_fetch_array($result) != 0 ) {
                $cont = false;
                $duplicateSubRouteId = $tempId[0];
            }
        }
        
        return $duplicateSubRouteId;
    } // End - Check $newSubRouteIds if there is already a created one in SubRoute
    
    // Start - Add data in DocumentRoute
    function addDocRoute($routeName, $documentSetupId){
        $query =
            "
                INSERT INTO DocumentRoute (routeName, documentSetupId, routeLastUpdated) VALUES
                    ('$routeName', '$documentSetupId', NULL);
            ";

        mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
    } // End - Add data in DocumentRoute
    
    // Start - Get the value in $theString, depending on the passed $index
    function getTheValue($theString, $index, $valuePosition, $comparePosition) {
        $theValue = "";
        $continue = true;
        $theArray = explode(",", $theString);
        
        for ( $x = 0 ; $x < count($theArray) - 1 && $continue == true ; $x++ ) {
            $tempVar = explode("_", $theArray[$x]);
            
            if ( $tempVar[$comparePosition] == $index ) {
                $theValue = $tempVar[$valuePosition];
                $continue = false;
            }
        }
        
        return $theValue;
    } // End - Get the value in $theArray, depending on the passed $index
    
    // Start - Get the value on the string depending on the passed rowNum and stepNum
    // get the value using 2 index
    function getTheValue2Idx($theString, $rowNum, $stepNum, $indxOfVal, $indxOfRw, $indxOfStp) {
        $theValue = "";
        $continue = true;
        $theArray = explode(",", $theString);
        
        for ( $x = 0 ; $x < count($theArray) - 1 && $continue == true ; $x++ ) {
            $tempVar = explode("_", $theArray[$x]);
            
            if ( $tempVar[$indxOfRw] == $rowNum && $tempVar[$indxOfStp] == $stepNum ) {
                $theValue = $tempVar[$indxOfVal];
                $continue = false;
            }
        }
        
        return $theValue;
    } // End - Get the value on the string depending on the passed rowNum and stepNum
    
    // Start - Get the values on the string depending on the passed rowNum and stepNum
    // get the values using 2 index
    function getMultipleValue2Idx($theString, $rowNum, $stepNum) {
        $theValue = "";
        $theArray = explode(",", $theString);
        
        for ( $x = 0 ; $x < count($theArray) - 1 ; $x++ ) {
            $tempVar = explode("_", $theArray[$x]);
            
            if ( $tempVar[1] == $rowNum && $tempVar[2] == $stepNum ) {
                $theValue = $theValue . $tempVar[0] . ",";
            }
        }
        
        return $theValue;
    } // End - Get the values on the string depending on the passed rowNum and stepNum
    
    // Start - Add data in SubRoute
    function addSubRoute($newSubRouteIds, $newSubRouteNames) {
        $newSubRouteIds = explode(",", $newSubRouteIds);
        
        for ( $x = 0 ; $x < count($newSubRouteIds) - 1 ; $x++ ) {
            $tempId = explode("_", $newSubRouteIds[$x]);
            $tempName = getTheValue($newSubRouteNames, $tempId[1], 0, 1);
            
            $query =
                "
                    INSERT INTO SubRoute (subRouteId, subRouteName) VALUES
                        ('$tempId[0]', '$tempName');
                ";

            mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        }
    } // End - Add data in SubRoute
    
    // Start - Get the routeId of the newly created route
    function getRouteId($routeName, $documentSetupId) {
        $query =
            "
                SELECT *
                FROM DocumentRoute
                WHERE routeName = '$routeName' AND documentSetupId = '$documentSetupId' AND routeLastUpdated IS NULL
            ";
        
        $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        $row = mysql_fetch_array($result);
        
        return $row['routeId'];
    } // End - Get the routeId of the newly created route
    
    // Start - Insert data in RouteStep
    function addRouteStep($routeId, $subRouteIds, $newSubRouteIds, $subRouteSteps, $recipientRoles, $canBeCancelled, $recipientRow, $reqAttach, $userActivity, $category) {
        // Note : The addRouteStep() will also insert data in the following tables
        // * RouteStep_Dept_Pos
        // * RouteStep_Req_Attachment
        // * Step_SubRoute
        
        $flagFirst = true;
        $stepNumber = 1;
        $subRouteSteps = explode(",", $subRouteSteps);
        $initRouteLstStp = 0;
        $initRouteLstStpNum = 0;
        
        for ( $x = 0 ; $x < count($subRouteSteps) - 1 ; $x++ ) {
            $tempId = explode("_", $subRouteSteps[$x]);
            $currentRow = $tempId[1];
            $currentStep = $tempId[3];
            
            // Prepare the needed variables :
            // * $tempSubRouteId
            // * $stepNumber
            if ( $flagFirst == true ) {
                $prevRouteNum = $tempId[1];
                $flagFirst = false;
//                error_log(".....");
//                error_log("if ".$prevRouteNum);
            }
            else if ( $prevRouteNum == $tempId[1] ) {
                $stepNumber++;
//                error_log("else if ".$prevRouteNum." ".$tempId[1]);
            }
            else {
                // Get the $initRouteLstStpNum of the Initial route for the succeeding subroutes
                if ( $prevRouteNum == "1" ) {
                    $initRouteLstStpNum = $stepNumber;
                }
                
                $prevRouteNum = $tempId[1];
                
                // The start of step number of cancel and reject transaction is 1, 
                // while the start of other subroutes is the $initRouteLstStpNum
                if ( $tempId[1] == "2" || $tempId[1] == "3" ) {
                    $stepNumber = 1;
                }
                else {
                    $stepNumber = $initRouteLstStpNum + 1;
                }
                
                $tempSubRouteId = getTheValue($subRouteIds, $tempId[1], 0, 1); // Get the subRouteId
                
                if ( $tempSubRouteId == "newSubRoute" ) {
                    $tempSubRouteId = getTheValue($newSubRouteIds, $tempId[1], 0, 1); // Get the new subRouteId
                }
//                error_log("else ".$prevRouteNum." ".$tempId[1]." ".$tempSubRouteId);
            }
            
            $theRole = getTheValue2Idx($recipientRoles, $currentRow, $currentStep, 0, 1, 2); // Get the role for this step
            $cancelFlag = getTheValue2Idx($canBeCancelled, $currentRow, $currentStep, 0, 1, 2); // Get the cancel flag for this step
            $theUserActivity = getTheValue2Idx($userActivity, $currentRow, $currentStep, 0, 1, 2); // Get the userActivity in string
            $theCategory = getTheValue2Idx($category, $currentRow, $currentStep, 0, 1, 2); // Get the category in string
            
            // Prepare the $cancelFlag
            if ( $cancelFlag == "Yes" ) {
                $cancelFlag = 1;
            }
            else {
                $cancelFlag = 0;
            }
            
            // Prepare the query for the insertion of data in the table
            if ( $tempId[1] ==  "1" ) {
                $query =
                    "
                        INSERT INTO RouteStep (routeId, subRouteId, stepNumber, role, canBeCancelled, userActivity, category) VALUES
                            ($routeId, NULL, '$stepNumber', '$theRole', '$cancelFlag', '$theUserActivity', '$theCategory');
                    ";
            }
            else {
                $query =
                    "
                        INSERT INTO RouteStep (routeId, subRouteId, stepNumber, role, canBeCancelled, userActivity, category) VALUES
                            ($routeId, '$tempSubRouteId', '$stepNumber', '$theRole', '$cancelFlag', '$theUserActivity', '$theCategory');
                    ";
            }
            
            // Insert the data in the table
            mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
            
            // Get the stepId of the newly inserted data
            // Prepare the query
            if ( $tempId[1] ==  "1" ) {
                $query =
                    "   
                        SELECT *
                        FROM RouteStep
                        WHERE routeId = '$routeId' AND subRouteId IS NULL AND stepNumber = '$stepNumber'
                    ";
            }
            else {
                $query =
                    "   
                        SELECT *
                        FROM RouteStep
                        WHERE routeId = '$routeId' AND subRouteId = '$tempSubRouteId' AND stepNumber = '$stepNumber'
                    ";
            }
            
            $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
            $row = mysql_fetch_array($result);
            $stepId = $row['stepId'];
            
            addDeptPos($stepId, $recipientRow, $currentRow, $currentStep); // Add data in RouteStep_Dept_Pos
            addReqAttach($stepId, $reqAttach, $currentRow, $currentStep); // Add data in RouteStep_Req_Attachment
            
            // Get the $initRouteLstStp, initial route last step for the adding data in Step_SubRoute
            if ( $tempId[1] ==  "1" ) {
                $initRouteLstStp = $stepId;
            }
        } // End of outer for loop
        
        // Add now the data in Step_SubRoute if there is data in $subRouteIds
        if ( $subRouteIds != "" ) {
            addStepSubRoute($initRouteLstStp, $subRouteIds, $newSubRouteIds);
        }
    } // End - Insert data in RouteStep
    
    // Start - Insert data in RouteStep_Req_Attachment depending on the passed parameters
    function addReqAttach($stepId, $reqAttach, $currentRow, $currentStep) {
        $varReqAttachs = getMultipleValue2Idx($reqAttach, $currentRow, $currentStep);
        $varReqAttachs = explode(",", $varReqAttachs);
        
        // Iterate the adding of data in RouteStep_Req_Attachment
        for ( $x = 0 ; $x < count($varReqAttachs) - 1 ; $x++ ) {
            $query =
                "
                    INSERT INTO RouteStep_Req_Attachment (stepId, documentSetupId) VALUES
                        ('$stepId', '$varReqAttachs[$x]');
                ";
            mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        }
    } // End - Insert data in RouteStep_Req_Attachment depending on the passed parameters
    
    // Start - Insert data in RouteStep_Dept_Pos depending on the passed parameters
    function addDeptPos($stepId, $recipientRow, $currentRow, $currentStep) {
        $varRecipients = getMultipleValue2Idx($recipientRow, $currentRow, $currentStep);
        $varRecipients = explode(",", $varRecipients);
        
        // Iterate the adding of data in RouteStep_Dept_Pos
        for ( $x = 0 ; $x < count($varRecipients) - 1 ; $x++ ) {
            $query =
                "
                    INSERT INTO RouteStep_Dept_Pos (stepId, deptPosId) VALUES
                        ('$stepId', '$varRecipients[$x]');
                ";
            mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        }
    } // End - Insert data in RouteStep_Dept_Pos depending on the passed parameters
    
    // Start - Add now the data in Step_SubRoute if there is data in $subRouteIds
    function addStepSubRoute($initRouteLstStp, $subRouteIds, $newSubRouteIds) {
        $subRouteIds = explode(",", $subRouteIds);
        
        for ( $x = 0 ; $x < count($subRouteIds) - 1 ; $x++ ) {
            $tempId = explode("_", $subRouteIds[$x]);
                
            if ( $tempId[0] == "newSubRoute" ) {
                $tempSubRouteId = getTheValue($newSubRouteIds, $tempId[1], 0, 1); // Get the new subRouteId
            }
            else {
                $tempSubRouteId = $tempId[0];
            }
            
            if (  $tempId[0] != "REJECTTRAC" &&  $tempId[0] != "CANCELTRAC" ) { 
                // Insert data in Step_SubRoute
                $query =
                    "
                        INSERT INTO Step_SubRoute (stepId, subRouteId) VALUES
                            ('$initRouteLstStp', '$tempSubRouteId');
                    ";
                mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
            }
        }
    } // End - Add now the data in Step_SubRoute if there is data in $subRouteIds
    
    // Start - Update the routeLastUpdated of the $updateRouteId
    function lastUpdateRoute($updateRouteId) {
        // Get current date and time
        date_default_timezone_set('Asia/Singapore');
        $currentDateTime= date("Y-m-d H:i:s" , time());
        
        $query = 
        "
            UPDATE DocumentRoute
            SET routeLastUpdated = '$currentDateTime'
            WHERE routeId = '$updateRouteId'
        ";
    
        mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
    } // End - Update the routeLastUpdated of the $updateRouteId
    
//********************************************************************************************************

session_start();

// Start - Checker for those users who will just go to the page by typing directly in the url.
if ($_POST) {
    include('../includes/siteConfig.php');
    
    // connect to database
    $connect = mysql_connect($hostName,$rootName,$dBasePassword) or die ('Unable to connect!');
    mysql_select_db($dBaseName) or die ('Unable to select database!');
    
    // Get the data from post 
    $updateRouteId = $_POST['routeId'];
    $routeName = $_POST['routeName'];
    $documentSetupId = $_POST['documentSetupId'];
    $subRouteIds = $_POST['subRouteIds'];
    $newSubRouteIds = $_POST['newSubRouteIds'];
    $newSubRouteNames = $_POST['newSubRouteNames'];
    $subRouteSteps = $_POST['subRouteSteps'];
    $recipientRoles = $_POST['recipientRoles'];
    $canBeCancelled = $_POST['canBeCancelled'];
    $reqAttach = $_POST['reqAttach'];
    $recipientRow = $_POST['recipientRow'];
    $userActivity = $_POST['userActivity'];
    $category = $_POST['category'];
    lastUpdateRoute($updateRouteId);
    
    // Check duplicate of name  
//    if ( nameNotExist($routeName) == true ) {
        // Check the following:
        // * No existing subRouteId that will be created
        $duplicateSubRouteId = checkNewSubRouteId($newSubRouteIds);
        
        if ( $duplicateSubRouteId == "" ) {
            addDocRoute($routeName, $documentSetupId); // Add data in DocumentRoute
            $routeId = getRouteId($routeName, $documentSetupId);
            addSubRoute($newSubRouteIds, $newSubRouteNames); // Add data in SubRoute
            addRouteStep($routeId, $subRouteIds, $newSubRouteIds, $subRouteSteps, $recipientRoles, $canBeCancelled, $recipientRow, $reqAttach, $userActivity, $category); // Add data in RouteStep

            $jsondata = array();
            $jsondata['msg'] = $routeName." route was successfully edited.";
            $jsondata['flag'] = "success";
            $feed[] = $jsondata;
            echo json_encode($feed);
        }
        else {
            $jsondata = array();
            $jsondata['msg'] = "Error : Sub Route Id ".$duplicateSubRouteId." already exists.";
            $jsondata['flag'] = "error";
            $feed[] = $jsondata;
            echo json_encode($feed);
        }
//    } // End first outer if
//    else {
//        $jsondata = array();
//        $jsondata['msg'] = "Error : ".$routeName." route name already exists.";
//        $jsondata['flag'] = "error";
//        $feed[] = $jsondata;
//        echo json_encode($feed);
//    } // End first outer else
    
        
    
    mysql_close($connect);
}   // End - Checker for those users who will just go to the page by typing directly in the url.
else {
    echo "You are not authorized to view this page. This incident will be reported immediately.";
}
?>
